/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package it.nyquistmeteocal.boundary;

import it.nyquistmeteocal.entity.Notification;
import java.security.Principal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

/**
 *
 * @author gatb27
 */
@Stateless
public class NotificationManager {
    
    @PersistenceContext
    public EntityManager nm;
    
    @Inject
    Principal principal;
    
    @EJB
    private UserManager um;
    
    private String sqlQuery;
    private String LogUser;
    
    
    
    
    public void saveNotification(Notification notification) {
        nm.persist(notification);     
    }
    
    public void deleteNotification(Notification notification) {
        nm.remove(notification);
    }
    
    
    
    public List<String> getRecipients(Integer eventid) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        sqlQuery = "SELECT calendarevents.useremail "
                + "FROM calendarevents, event "
                + "WHERE calendarevents.eventid=? "
                + "AND event.id=calendarevents.eventid "
                + "AND event.creator!=calendarevents.useremail";
        List<String> records = new ArrayList<String>();
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setInt(1, eventid);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                String string = rs.getString(1);
                records.add(string);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<String> getRecipients2(Integer eventid) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        sqlQuery = "SELECT calendarevents.useremail "
                + "FROM calendarevents, event "
                + "WHERE calendarevents.eventid=? "
                + "AND event.id=calendarevents.eventid";
        List<String> records = new ArrayList<String>();
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setInt(1, eventid);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                String string = rs.getString(1);
                records.add(string);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<String> getRecipients3(Integer eventid) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        sqlQuery = "SELECT calendarevents.useremail "
                + "FROM calendarevents, event "
                + "WHERE calendarevents.eventid=? "
                + "AND event.id=calendarevents.eventid "
                + "AND event.creator=calendarevents.useremail";
        List<String> records = new ArrayList<String>();
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setInt(1, eventid);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                String string = rs.getString(1);
                records.add(string);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Integer> getNotification1() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        sqlQuery = "SELECT eventid "
                + "FROM notification "
                + "WHERE type='badWeather'";
        List<Integer> records = new ArrayList<Integer>();
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Integer integer = rs.getInt(1);
                records.add(integer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Integer> getNotification2() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        sqlQuery = "SELECT eventid "
                + "FROM notification "
                + "WHERE type='tomorrowRainy'";
        List<Integer> records = new ArrayList<Integer>();
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Integer integer = rs.getInt(1);
                records.add(integer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Notification> getInvites() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT notification.text, notification.id, notification.type, "
                + "notification.eventid "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='invite'";
        
        List<Notification> records = new ArrayList<Notification>();
        try{
            
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Notification notification = new Notification();
                notification.setText(rs.getString(1));
                notification.setId(rs.getInt(2));
                notification.setType(rs.getString(3));
                notification.setEventid(rs.getInt(4));
                records.add(notification);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Notification> getUpdates() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT notification.text, notification.id, notification.type, "
                + "notification.eventid "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='update'";
        
        List<Notification> records = new ArrayList<Notification>();
        try{
            
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Notification notification = new Notification();
                notification.setText(rs.getString(1));
                notification.setId(rs.getInt(2));
                notification.setType(rs.getString(3));
                notification.setEventid(rs.getInt(4));
                records.add(notification);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Notification> getDeletes() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT notification.text, notification.id, notification.type, "
                + "notification.eventid "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='delete'";
        
        List<Notification> records = new ArrayList<Notification>();
        try{
            
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Notification notification = new Notification();
                notification.setText(rs.getString(1));
                notification.setId(rs.getInt(2));
                notification.setType(rs.getString(3));
                notification.setEventid(rs.getInt(4));
                records.add(notification);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Notification> getTomorrowRainy() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT notification.text, notification.id, notification.type, "
                + "notification.eventid "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='tomorrowRainy'";
        
        List<Notification> records = new ArrayList<Notification>();
        try{
            
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Notification notification = new Notification();
                notification.setText(rs.getString(1));
                notification.setId(rs.getInt(2));
                notification.setType(rs.getString(3));
                notification.setEventid(rs.getInt(4));
                records.add(notification);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
    
    public List<Notification> getBadWeather() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT notification.text, notification.id, notification.type, "
                + "notification.eventid "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='badWeather'";
        
        List<Notification> records = new ArrayList<Notification>();
        try{
            
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            
            while(rs.next()) {
                Notification notification = new Notification();
                notification.setText(rs.getString(1));
                notification.setId(rs.getInt(2));
                notification.setType(rs.getString(3));
                notification.setEventid(rs.getInt(4));
                records.add(notification);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    
    public Integer findUserNotification(Integer notid) {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer unid = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT id "
                + "FROM usernotification "
                + "WHERE usermail=? AND notificationid=?";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.setInt(2, notid);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                unid = rs.getInt(1);
                System.out.println("the usernotificationid IS: " + unid);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("the usernotificationid IIS: " + unid);
        return unid;
    }
    
    public Integer countInvites() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer number = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT count(*) "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='invite'";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                number = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }
    
    public Integer countUpdates() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer number = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT count(*) "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='update'";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                number = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }
    
    public Integer countDeletes() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer number = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT count(*) "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='delete'";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                number = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }
    
    public Integer countTomorrowRainy() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer number = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT count(*) "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='tomorrowRainy'";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                number = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }
    
    public Integer countBadWeather() {
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection con = getConnection();
        Integer number = -1;
        
        LogUser = um.getLoggedUser().getEmail();
        
        sqlQuery = "SELECT count(*) "
                + "FROM notification, usernotification "
                + "WHERE notification.id=usernotification.notificationid "
                + "AND usernotification.usermail=? "
                + "AND notification.type='badWeather'";
         
        try{
            pst = con.prepareStatement(sqlQuery);
            pst.setString(1, LogUser);
            pst.execute();
            rs = pst.getResultSet();
            while (rs.next()) {
                number = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }
    
        
    public Connection getConnection() {
        Connection con = null;

        String url = "jdbc:mysql://localhost:3306/nyreg";
        String user = "monty";
        String password = "some_pass";
        try {
            con = DriverManager.getConnection(url, user, password);
            System.out.println("Connection completed. ");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
        }
        return con;
    }
}
